{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "\n",
    "sc = (SparkSession.builder.appName('app16-3') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "band = sc.read.table('sqlzoo.band')\n",
    "composer = sc.read.table('sqlzoo.composer')\n",
    "composition = sc.read.table('sqlzoo.composition')\n",
    "concert = sc.read.table('sqlzoo.concert')\n",
    "has_composed = sc.read.table('sqlzoo.has_composed')\n",
    "musician = sc.read.table('sqlzoo.musician')\n",
    "performance = sc.read.table('sqlzoo.performance')\n",
    "performer = sc.read.table('sqlzoo.performer')\n",
    "place = sc.read.table('sqlzoo.place')\n",
    "plays_in = sc.read.table('sqlzoo.plays_in')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**List the name and town of birth of any performer born in the same city as James First.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>place_town</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alan Fluff</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy Jones</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Theo Mengel</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        m_name place_town\n",
       "0   Alan Fluff     London\n",
       "1   Andy Jones     London\n",
       "2  Theo Mengel     London"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(musician.filter(musician['m_name'] != 'James First')\n",
    " .join(musician.filter(musician['m_name']=='James First')\n",
    "       .join(place, on=(musician['born_in']==place['place_no']))\n",
    "       .select('born_in', 'place_town'),\n",
    "       on='born_in')\n",
    " .select('m_name', 'place_town')\n",
    " .orderBy('m_name')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Create a list showing for EVERY musician born in Britain the number of compositions and the number of instruments played.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_no</th>\n",
       "      <th>m_name</th>\n",
       "      <th>n_composition</th>\n",
       "      <th>n_instrument</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>Alan Fluff</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>Andy Jones</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>Davis Heavan</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>Elsie James</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Fred Bloggs</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17</td>\n",
       "      <td>Freda Miles</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4</td>\n",
       "      <td>Harriet Smithson</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Forte</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>Helen Smyth</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>James First</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>James Quick</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>21</td>\n",
       "      <td>James Steeple</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>Jeff Dawn</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20</td>\n",
       "      <td>Louise Simpson</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>13</td>\n",
       "      <td>Lovely Time</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>9</td>\n",
       "      <td>Phil Hot</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11</td>\n",
       "      <td>Rose Spring</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>22</td>\n",
       "      <td>Steven Chaytors</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7</td>\n",
       "      <td>Sue Little</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>6</td>\n",
       "      <td>Theo Mengel</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>15</td>\n",
       "      <td>Tony Smythe</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    m_no            m_name  n_composition  n_instrument\n",
       "0     14        Alan Fluff              0             2\n",
       "1     19        Andy Jones              1             0\n",
       "2     12      Davis Heavan              0             3\n",
       "3     18       Elsie James              0             3\n",
       "4      1       Fred Bloggs              2             0\n",
       "5     17       Freda Miles              2             0\n",
       "6      4  Harriet Smithson              0             2\n",
       "7      8       Harry Forte              2             3\n",
       "8      3       Helen Smyth              1             1\n",
       "9      5       James First              4             1\n",
       "10    16       James Quick              0             2\n",
       "11    21     James Steeple              0             0\n",
       "12    10         Jeff Dawn              1             2\n",
       "13     2        John Smith              0             3\n",
       "14    20    Louise Simpson              0             3\n",
       "15    13       Lovely Time              2             0\n",
       "16     9          Phil Hot              3             0\n",
       "17    11       Rose Spring              2             0\n",
       "18    22   Steven Chaytors              0             0\n",
       "19     7        Sue Little              2             1\n",
       "20     6       Theo Mengel              0             3\n",
       "21    15       Tony Smythe              1             0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (musician\n",
    "     .join(place.filter(place['place_country'].isin('England', 'Scotland')),\n",
    "           on=(musician['born_in']==place['place_no']), how='left')\n",
    "     .join(composer, on=(musician['m_no']==composer['comp_is']), how='left')\n",
    "     .join(has_composed, on=(composer['comp_no']==has_composed['cmpr_no']), how='left')\n",
    "     .join(composition, on=(has_composed['cmpn_no']==composition['c_no']), how='left')\n",
    "     .groupBy('m_no', 'm_name')\n",
    "     .agg(count('c_no').alias('n_composition')))\n",
    "p = (musician.join(performer, on=(musician['m_no']==performer['perf_is']), how='left')\n",
    "     .select('m_no', 'instrument')\n",
    "     .distinct()\n",
    "     .groupBy('m_no')\n",
    "     .agg(count('instrument').alias('n_instrument')))\n",
    "t.join(p, on='m_no').orderBy('m_name').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Give the band name, conductor and contact of the bands performing at the most recent concert in the Royal Albert Hall.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>band_name</th>\n",
       "      <th>contact</th>\n",
       "      <th>conductor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Somebody Loves this</td>\n",
       "      <td>Theo Mengel</td>\n",
       "      <td>Alan Fluff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             band_name      contact   conductor\n",
       "0  Somebody Loves this  Theo Mengel  Alan Fluff"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rah = (concert.filter(concert['concert_venue']=='Royal Albert Hall')\n",
    "       .orderBy(col('con_date').desc())\n",
    "       .limit(1))\n",
    "(band\n",
    " .join(musician.withColumnRenamed('m_name', 'contact')\n",
    "       .alias('mus1'), \n",
    "       on=(band['band_contact']==col('mus1.m_no')))\n",
    " .join(performance, on=(band['band_no']==performance['gave']))\n",
    " .join(musician.withColumnRenamed('m_name', 'conductor')\n",
    "       .alias('mus2'),\n",
    "       on=(performance['conducted_by']==col('mus2.m_no')))\n",
    " .join(rah, on=(performance['performed_in']==rah['concert_no']))\n",
    " .select('band_name', 'contact', 'conductor')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Give a list of musicians associated with Glasgow. Include the name of the musician and the nature of the association - one or more of 'LIVES_IN', 'BORN_IN', 'PERFORMED_IN' AND 'IN_BAND_IN'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>assoc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Louise Simpson</td>\n",
       "      <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Steven Chaytors</td>\n",
       "      <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lovely Time</td>\n",
       "      <td>BORN_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>James Steeple</td>\n",
       "      <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andy Jones</td>\n",
       "      <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Louise Simpson</td>\n",
       "      <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Lovely Time</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Freda Miles</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Elsie James</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tony Smythe</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Alan Fluff</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Jeff Dawn</td>\n",
       "      <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Harriet Smithson</td>\n",
       "      <td>LIVES_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Jeff Dawn</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Davis Heavan</td>\n",
       "      <td>IN_BAND_IN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              m_name       assoc\n",
       "0     Louise Simpson     BORN_IN\n",
       "1    Steven Chaytors     BORN_IN\n",
       "2        Lovely Time     BORN_IN\n",
       "3      James Steeple    LIVES_IN\n",
       "4         Andy Jones    LIVES_IN\n",
       "5     Louise Simpson    LIVES_IN\n",
       "6        Lovely Time  IN_BAND_IN\n",
       "7        Freda Miles  IN_BAND_IN\n",
       "8        Elsie James  IN_BAND_IN\n",
       "9        Tony Smythe  IN_BAND_IN\n",
       "10        Alan Fluff  IN_BAND_IN\n",
       "11         Jeff Dawn    LIVES_IN\n",
       "12  Harriet Smithson    LIVES_IN\n",
       "13         Jeff Dawn  IN_BAND_IN\n",
       "14      Davis Heavan  IN_BAND_IN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1 = (musician.join(place, on=(musician['born_in']==place['place_no']))\n",
    "      .select('m_name', 'place_town')\n",
    "      .withColumn('assoc', lit('BORN_IN')))\n",
    "t2 = (musician.join(place, on=(musician['living_in']==place['place_no']))\n",
    "      .select('m_name', 'place_town')\n",
    "      .withColumn('assoc', lit('LIVES_IN')))\n",
    "t3 = (musician.join(performer, on=(musician['m_no']=='perf_is'))\n",
    "      .join(plays_in, on=(performer['perf_no']==plays_in['player']))\n",
    "      .join(performance, on=(plays_in['band_id']==performance['gave']))\n",
    "      .join(concert, on=(performance['performed_in']==concert['concert_no']))\n",
    "      .join(place, on=(concert['concert_in']==place['place_no']))\n",
    "      .select('m_name', 'place_town')\n",
    "      .withColumn('assoc', lit('PERFORMED_IN')))\n",
    "t4 = (musician.join(plays_in, on=(musician['m_no']==plays_in['player']))\n",
    "      .join(band, on=(plays_in['band_id']==band['band_no']))\n",
    "      .join(place, on=(band['band_home']==place['place_no']))\n",
    "      .select('m_name', 'place_town')\n",
    "      .withColumn('assoc', lit('IN_BAND_IN')))\n",
    "(t1.union(t2).union(t3).union(t4)\n",
    " .filter(col('place_town')=='Glasgow')\n",
    " .select('m_name', 'assoc')\n",
    " .distinct()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Jeff Dawn plays in a band with someone who plays in a band with Sue Little. Who is it and what are the bands?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>Sue's band</th>\n",
       "      <th>Jeff's band</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>BBSO</td>\n",
       "      <td>AASO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       m_name Sue's band Jeff's band\n",
       "0  John Smith       BBSO        AASO"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sue_band = (musician.filter(musician['m_name']=='Sue Little')\n",
    "            .join(performer, on=(musician['m_no']==performer['perf_is']))\n",
    "            .join(plays_in, on=(performer['perf_no']==plays_in['player']))\n",
    "            .join(band, on=(plays_in['band_id']==band['band_no']))\n",
    "            .select('band_id', 'band_name'))\n",
    "sue_coplayers = (musician\n",
    "                 .join(performer, on=(musician['m_no']==performer['perf_is']))\n",
    "                 .join(plays_in, on=(performer['perf_no']==plays_in['player']))\n",
    "                 .join(sue_band, on='band_id'))\n",
    "jeff_band = (musician.filter(musician['m_name']=='Jeff Dawn')\n",
    "             .join(performer, on=(musician['m_no']==performer['perf_is']))\n",
    "             .join(plays_in, on=(performer['perf_no']==plays_in['player']))\n",
    "             .join(band, on=(plays_in['band_id']==band['band_no']))\n",
    "             .select('band_id', 'band_name'))\n",
    "jeff_coplayers = (musician\n",
    "                 .join(performer, on=(musician['m_no']==performer['perf_is']))\n",
    "                 .join(plays_in, on=(performer['perf_no']==plays_in['player']))\n",
    "                 .join(jeff_band, on='band_id'))\n",
    "(sue_coplayers.select(\n",
    "    'm_no', col('band_name').alias('Sue\\'s band'), 'm_name')\n",
    " .join(jeff_coplayers\n",
    "       .select('m_no', col('band_name').alias('Jeff\\'s band')), on='m_no')\n",
    " .select('m_name', 'Sue\\'s band', 'Jeff\\'s band')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
